﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Administration.Catalog
{
   public partial class ReviewProgramRepo
    {
       public object SelectScalar(SQL.Function.Aggregate function, string column,string parameters)
       {
           object _result = null;
           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   StringBuilder sbQuery = new StringBuilder();
                   switch (function)
                   {
                       case SQL.Function.Aggregate.Max:
                           sbQuery.AppendFormat("SELECT MAX({0}) FROM master.ReviewProgram {1} ", column,parameters);
                           break;
                       case SQL.Function.Aggregate.Min:
                           sbQuery.AppendFormat("SELECT MIN({0}) FROM master.ReviewProgram {1} ", column, parameters);
                           break;
                       case SQL.Function.Aggregate.Distinct:
                           sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM master.ReviewProgram {1} ", column, parameters);
                           break;
                       case SQL.Function.Aggregate.Count:
                           sbQuery.AppendFormat("SELECT COUNT({0}) FROM master.ReviewProgram {1} ", column, parameters);
                           break; 
                       case SQL.Function.Aggregate.Sum:
                           sbQuery.AppendFormat("SELECT SUM({0}) FROM master.ReviewProgram {1} ", column, parameters);
                           break;
                       case SQL.Function.Aggregate.Avg:
                           sbQuery.AppendFormat("SELECT AVG({0}) FROM master.ReviewProgram {1} ", column, parameters);
                           break;
                       default:
                           // do nothing 
                           break;
                   }

                   cmd.CommandText = sbQuery.ToString();
                   cmd.CommandType = CommandType.Text;
                   try
                   {
                       conn.Open();
                       _result = cmd.ExecuteScalar();
                   }
                   catch (Exception ex)
                   {
                       throw (ex);
                   }
                   finally
                   {
                       if (conn.State == ConnectionState.Open) conn.Close();
                   }
               }
           }
           return _result;
       }

       public List<ReviewProgram> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
       {
           List<ReviewProgram> tt = new List<ReviewProgram>();
           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   int startRow = ((currentPage - 1) * pageSize) + 1;
                   int endRow = (startRow + pageSize);

                   StringBuilder sbQuery = new StringBuilder();
                   sbQuery.AppendFormat(" WITH result_set AS ");
                   sbQuery.AppendFormat(" ( ");
                   sbQuery.AppendFormat("    SELECT ");
                   sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                   sbQuery.AppendFormat("      ProgramID,RowStatus, ProgramName, PeriodYear, ProgramPeriod, StartDate, EndDate, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy ");
                   sbQuery.AppendFormat("    FROM ");
                   sbQuery.AppendFormat("      master.ReviewProgram ");
                   sbQuery.AppendFormat("      {0} ",parameters);
                   sbQuery.AppendFormat(" ) ");
                   sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                   cmd.CommandText = sbQuery.ToString();
                   cmd.CommandType = CommandType.Text;
                   try
                   {
                       conn.Open();
                       using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                       {
                           while (dr.Read())
                           {
                               ReviewProgram t = new ReviewProgram();
                               t.ProgramID = dr[ReviewProgram.Fields.ProgramID] == DBNull.Value ? 0 : int.Parse(dr[ReviewProgram.Fields.ProgramID].ToString());
                               t.RowStatus = dr[Competency.Fields.RowStatus] == DBNull.Value ? false : bool.Parse(dr[Competency.Fields.RowStatus].ToString());
                               t.ProgramName = dr[ReviewProgram.Fields.ProgramName] == DBNull.Value ? string.Empty : dr[ReviewProgram.Fields.ProgramName].ToString();
                               t.PeriodYear = dr[ReviewProgram.Fields.PeriodYear] == DBNull.Value ? string.Empty : dr[ReviewProgram.Fields.PeriodYear].ToString();
                               t.ProgramPeriod = dr[ReviewProgram.Fields.ProgramPeriod] == DBNull.Value ? string.Empty : dr[ReviewProgram.Fields.ProgramPeriod].ToString();
                               t.StartDate = dr[ReviewProgram.Fields.StartDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[ReviewProgram.Fields.StartDate].ToString());
                               t.EndDate = dr[ReviewProgram.Fields.EndDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[ReviewProgram.Fields.EndDate].ToString());
                               t.CreatedDate = dr[ReviewProgram.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[ReviewProgram.Fields.CreatedDate].ToString());
                               t.CreatedBy = dr[ReviewProgram.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[ReviewProgram.Fields.CreatedBy].ToString();
                               t.UpdatedDate = dr[ReviewProgram.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[ReviewProgram.Fields.UpdatedDate].ToString());
                               t.UpdatedBy = dr[ReviewProgram.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[ReviewProgram.Fields.UpdatedBy].ToString();
                               tt.Add(t);
                               t.Dispose();
                           }
                       }
                   }
                   catch (Exception ex)
                   {
                       throw (ex);
                   }
                   finally
                   {
                       if (conn.State == ConnectionState.Open) conn.Close();
                   }
               }
           }
           return tt;
       }

       public DataTable GetDataTableReport(string parameters)
       {
           DataTable dt = new DataTable();

           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {

                    StringBuilder sbQuery = new StringBuilder();
                    //sbQuery.AppendLine("SELECT   ROW_NUMBER() OVER(ORDER BY B.ProgramName DESC) AS Row, B.ProgramName NamaProgram");
                    //sbQuery.AppendLine(",B.PeriodYear Tahun");
                    //sbQuery.AppendLine(",B.ProgramPeriod Periode");
                    //sbQuery.AppendLine(",convert(varchar, B.StartDate , 105) TglAwal");
                    //sbQuery.AppendLine(",convert(varchar, B.EndDate , 105) TglAkhir");
                    //sbQuery.AppendLine(",A.CreatedBy UserReview");
                    //sbQuery.AppendLine(",D.PositionName Jabatan");
                    //sbQuery.AppendLine(",(SELECT TOP 1 PositionName FROM master.Position WHERE PositionID=D.PositionSuperior) Atasan");
                    //sbQuery.AppendLine("FROM transactions.ReviewJobDescription A");
                    //sbQuery.AppendLine("INNER JOIN master.ReviewProgram B ON A.ProgramID = B.ProgramID");
                    //sbQuery.AppendLine("INNER JOIN transactions.JobDescription  C ON A.JobDescID = C.JobDescID");
                    //sbQuery.AppendLine("INNER JOIN master.Position D ON C.PositionID = D.PositionID");
                    //sbQuery.AppendLine("WHERE B.PeriodYear=@PeriodYear");
                    //sbQuery.AppendLine("AND B.ProgramPeriod=@ProgramPeriod");

                    sbQuery.AppendLine("SELECT   ROW_NUMBER() OVER(ORDER BY B.IsReview DESC) AS Row");
                    sbQuery.AppendLine(",(CASE WHEN B.IsReview=1 THEN 'Sudah' ELSE 'Belum' END) Status");
                    sbQuery.AppendLine(",B.CreatedBy Reviewer");
                    sbQuery.AppendLine(",convert(varchar, B.CreatedDate , 105)  ReviewerDate");
                    sbQuery.AppendLine(",C.ProgramName NamaProgram");
                    sbQuery.AppendLine(",C.PeriodYear Tahun");
                    sbQuery.AppendLine(",C.ProgramPeriod Periode");
                    sbQuery.AppendLine(",D.PositionName UserReview");
                    sbQuery.AppendLine(",(SELECT X.PositionName FROM master.Position X WHERE PositionID =(");
                    sbQuery.AppendLine("SELECT z.PositionId FROM master.recUser z WHERE z.UserName=B.CreatedBy)) JabatanReviewer");
                    sbQuery.AppendLine("FROM transactions.JobDescription A LEFT JOIN transactions.ReviewJobDescription B");
                    sbQuery.AppendLine("ON A.JobDescID = B.JobDescID");
                    sbQuery.AppendLine("left JOIN master.ReviewProgram C ON B.ProgramID=C.ProgramID");
                    sbQuery.AppendLine("left join master.Position D ON D.PositionID = A.PositionID");
                    sbQuery.Append(parameters);
                    //sbQuery.AppendLine("WHERE C.PeriodYear=@PeriodYear");
                    //sbQuery.AppendLine("AND C.ProgramPeriod=@ProgramPeriod");


                   cmd.CommandText = sbQuery.ToString();
                   //cmd.Parameters.Add("@PeriodYear", SqlDbType.Int).Value = tahun;
                   //cmd.Parameters.Add("@ProgramPeriod", SqlDbType.Int).Value = periode;
                   //cmd.BindByName = true;


                   SqlDataAdapter da = new SqlDataAdapter();
                   da.SelectCommand = cmd;

                   try
                   {
                       conn.Open();
                       da.Fill(dt);
                   }
                   catch (Exception ex)
                   {
                       throw (ex);
                   }
                   finally
                   {
                       if (conn.State == ConnectionState.Open) conn.Close();
                   }

               }
           }

           return dt;

       }

       public bool Save(ReviewProgram domain,int programIDLast)
       {
           bool result = false;

           using (SqlConnection conn = new SqlConnection(connectionString))
           {

               conn.Open();
               SqlTransaction txn = conn.BeginTransaction();
               try
               {
                   #region Insert Data Review Program


                   string query = "INSERT INTO master.ReviewProgram(RowStatus, ProgramName, PeriodYear, ProgramPeriod, StartDate, EndDate, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@RowStatus, @ProgramName, @PeriodYear, @ProgramPeriod, @StartDate, @EndDate, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                   SqlCommand cmd = new SqlCommand(query, conn);
                   cmd.CommandType = CommandType.Text;
                   cmd.Parameters.Add("@RowStatus", SqlDbType.Bit).Value = (object)domain.RowStatus ?? DBNull.Value;
                   cmd.Parameters.Add("@ProgramName", SqlDbType.VarChar).Value = (object)domain.ProgramName ?? DBNull.Value;
                   cmd.Parameters.Add("@PeriodYear", SqlDbType.Char).Value = (object)domain.PeriodYear ?? DBNull.Value;
                   cmd.Parameters.Add("@ProgramPeriod", SqlDbType.Char).Value = (object)domain.ProgramPeriod ?? DBNull.Value;
                   cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = (object)domain.StartDate ?? DBNull.Value;
                   cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = (object)domain.EndDate ?? DBNull.Value;
                   cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                   cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                   cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                   cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
          

                   cmd.Transaction = txn;
                   cmd.ExecuteNonQuery();
                   cmd.Dispose();

                   #endregion

                   #region Ambil ProgramID

                   string queryjobdescid = @"SELECT ProgramID FROM master.ReviewProgram WHERE CreatedBy=@CreatedBy AND CreatedDate=@CreatedDate";

                   SqlCommand cmdProgram = new SqlCommand(queryjobdescid, conn);
                   cmdProgram.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                   cmdProgram.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                   cmdProgram.Transaction = txn;
                   var programid = cmdProgram.ExecuteScalar();
                   int resultProgramID = programid == DBNull.Value ? 0 : int.Parse(programid.ToString());
                   cmdProgram.Dispose();

                   #endregion


                   #region AMBIL data JobTitle berdasar ProgramIDTerakhir

                   string queryRespon = @"SELECT * FROM master.JobTitle WHERE ProgramID=@ProgramID";

                   SqlCommand cmdRespon = new SqlCommand(queryRespon, conn);
                   cmdRespon.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDLast;
                   cmdRespon.Transaction = txn;

                   DataSet ds = new DataSet();
                   SqlDataAdapter dap = new SqlDataAdapter(cmdRespon);
                   dap.Fill(ds);
                   dap.Dispose();
                   cmdRespon.Dispose();
                   foreach (DataRow dr in ds.Tables[0].Rows)
                   {
                       #region Insert Data JobTitle

                       query = "INSERT INTO master.JobTitle(RowStatus, PositionID, MinEducation, Major, MinExperience, WorkingExperience, Specification, FunctionalCompetency, Challenge, Authority, ImageOrganitation,ProgramID, CreatedDate, CreatedBy) VALUES(@RowStatus, @PositionID, @MinEducation, @Major, @MinExperience, @WorkingExperience, @Specification, @FunctionalCompetency, @Challenge, @Authority, @ImageOrganitation,@ProgramID, @CreatedDate, @CreatedBy)";
                       SqlCommand cmdJobTitle = new SqlCommand(query, conn);
                       cmdJobTitle.CommandType = CommandType.Text;
                       cmdJobTitle.Parameters.Add("@RowStatus", SqlDbType.Bit).Value = (object)dr["RowStatus"]?? DBNull.Value;
                       cmdJobTitle.Parameters.Add("@PositionID", SqlDbType.Int).Value = (object)dr["PositionID"] ?? DBNull.Value;
                       cmdJobTitle.Parameters.Add("@MinEducation", SqlDbType.VarChar).Value = (object)dr["MinEducation"] ?? DBNull.Value;
                       cmdJobTitle.Parameters.Add("@Major", SqlDbType.VarChar).Value = (object)dr["Major"] ?? DBNull.Value;
                       cmdJobTitle.Parameters.Add("@MinExperience", SqlDbType.Text).Value = (object)dr["MinExperience"] ?? DBNull.Value;
                       cmdJobTitle.Parameters.Add("@WorkingExperience", SqlDbType.Text).Value = (object)dr["WorkingExperience"] ?? DBNull.Value;
                       cmdJobTitle.Parameters.Add("@Specification", SqlDbType.Text).Value = (object)dr["Specification"] ?? DBNull.Value;
                       cmdJobTitle.Parameters.Add("@FunctionalCompetency", SqlDbType.Text).Value = (object)dr["FunctionalCompetency"]?? DBNull.Value;
                       cmdJobTitle.Parameters.Add("@Challenge", SqlDbType.Text).Value = (object)dr["Challenge"] ?? DBNull.Value;
                       cmdJobTitle.Parameters.Add("@Authority", SqlDbType.Text).Value = (object)dr["Authority"] ?? DBNull.Value;
                       cmdJobTitle.Parameters.Add("@ImageOrganitation", SqlDbType.VarChar).Value = (object)dr["ImageOrganitation"] ?? DBNull.Value;
                       cmdJobTitle.Parameters.Add("@ProgramID", SqlDbType.Int).Value = resultProgramID;
                       cmdJobTitle.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                       cmdJobTitle.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;


                       cmdJobTitle.Transaction = txn;
                       cmdJobTitle.ExecuteNonQuery();
                       cmdJobTitle.Dispose();

                       #endregion
                   }

                   #endregion

                   #region Ambil data JobManagerialCompetency berdasar ProgramIDTerakhir

                   query = @"SELECT * FROM master.JobManagerialCompetency WHERE ProgramID=@ProgramID";

                   SqlCommand cmdMng = new SqlCommand(query, conn);
                   cmdMng.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDLast;
                   cmdMng.Transaction = txn;

                   ds = new DataSet();
                   dap = new SqlDataAdapter(cmdMng);
                   dap.Fill(ds);
                   dap.Dispose();
                   cmdMng.Dispose();
                   foreach (DataRow dr in ds.Tables[0].Rows)
                   {
                       #region Insert Data JobManagerialCompetency

                       query = "INSERT INTO master.JobManagerialCompetency(RowStatus, PositionID, CompetencyID, LevelCompetencyID,ProgramID, CreatedBy, CreatedDate) VALUES(@RowStatus, @PositionID, @CompetencyID, @LevelCompetencyID,@ProgramID, @CreatedBy, @CreatedDate)";
                       SqlCommand cmdMngCompt = new SqlCommand(query, conn);
                       cmdMngCompt.CommandType = CommandType.Text;
                       cmdMngCompt.Parameters.Add("@RowStatus", SqlDbType.Bit).Value = dr["RowStatus"].ToString();
                       cmdMngCompt.Parameters.Add("@PositionID", SqlDbType.Int).Value = dr["PositionID"].ToString();
                       cmdMngCompt.Parameters.Add("@CompetencyID", SqlDbType.Int).Value = dr["CompetencyID"].ToString();
                       cmdMngCompt.Parameters.Add("@LevelCompetencyID", SqlDbType.Int).Value = (object)dr["LevelCompetencyID"].ToString() ?? DBNull.Value;
                       cmdMngCompt.Parameters.Add("@ProgramID", SqlDbType.Int).Value = resultProgramID;
                       cmdMngCompt.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                       cmdMngCompt.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;


                       cmdMngCompt.Transaction = txn;
                       cmdMngCompt.ExecuteNonQuery();
                       cmdMngCompt.Dispose();

                       #endregion
                   }





                   #endregion


                   #region Ambil data JobDescription berdasar ProgramIDTerakhir

                   query = @"SELECT * FROM transactions.JobDescription WHERE ProgramID=@ProgramID";

                   SqlCommand cmdJobDesc = new SqlCommand(query, conn);
                   cmdJobDesc.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDLast;
                   cmdJobDesc.Transaction = txn;

                   ds = new DataSet();
                   dap = new SqlDataAdapter(cmdJobDesc);
                   dap.Fill(ds);
                   dap.Dispose();
                   cmdJobDesc.Dispose();
                   foreach (DataRow dr in ds.Tables[0].Rows)
                   {
                       #region Insert Data JobDescription


                       query  = "INSERT INTO transactions.JobDescription(RowStatus, PositionID, DepartmentID, Location, JobPurpose, Capex, Opex, DirectSubordinates, IndirectSubordinates, AppAccountID, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate, ReviewBy, ReviewDate, ProgramID) VALUES(@RowStatus, @PositionID, @DepartmentID, @Location, @JobPurpose, @Capex, @Opex, @DirectSubordinates, @IndirectSubordinates, @AppAccountID, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate, @ReviewBy, @ReviewDate, @ProgramID)";
                       SqlCommand cmdJobDescIns = new SqlCommand(query, conn);
                       cmdJobDescIns.CommandType = CommandType.Text;
                       cmdJobDescIns.Parameters.Add("@RowStatus", SqlDbType.Bit).Value = 0;
                       cmdJobDescIns.Parameters.Add("@PositionID", SqlDbType.Int).Value = int.Parse(dr["PositionID"].ToString());
                       cmdJobDescIns.Parameters.Add("@DepartmentID", SqlDbType.Int).Value =int.Parse(dr["DepartmentID"].ToString());
                       cmdJobDescIns.Parameters.Add("@Location", SqlDbType.VarChar).Value = (object)dr["Location"].ToString() ?? DBNull.Value;
                       cmdJobDescIns.Parameters.Add("@JobPurpose", SqlDbType.Text).Value = (object)dr["JobPurpose"].ToString() ?? DBNull.Value;
                       cmdJobDescIns.Parameters.Add("@Capex", SqlDbType.VarChar).Value = (object)dr["Capex"].ToString() ?? DBNull.Value;
                       cmdJobDescIns.Parameters.Add("@Opex", SqlDbType.VarChar).Value = (object)dr["Opex"].ToString() ?? DBNull.Value;
                       cmdJobDescIns.Parameters.Add("@DirectSubordinates", SqlDbType.VarChar).Value = (object)dr["DirectSubordinates"].ToString() ?? DBNull.Value;
                       cmdJobDescIns.Parameters.Add("@IndirectSubordinates", SqlDbType.VarChar).Value = (object)dr["IndirectSubordinates"].ToString() ?? DBNull.Value;
                       cmdJobDescIns.Parameters.Add("@AppAccountID", SqlDbType.Int).Value = (object) dr["AppAccountID"] ?? DBNull.Value;
                       cmdJobDescIns.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;// (object)domain.CreatedBy ?? DBNull.Value;
                       cmdJobDescIns.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                       cmdJobDescIns.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value =  DBNull.Value;
                       cmdJobDescIns.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = DBNull.Value;
                       cmdJobDescIns.Parameters.Add("@ReviewBy", SqlDbType.VarChar).Value =  DBNull.Value;
                       cmdJobDescIns.Parameters.Add("@ReviewDate", SqlDbType.DateTime).Value = DBNull.Value;
                       cmdJobDescIns.Parameters.Add("@ProgramID", SqlDbType.Int).Value = resultProgramID;


                       cmdJobDescIns.Transaction = txn;
                       cmdJobDescIns.ExecuteNonQuery();
                       cmdJobDescIns.Dispose();

                       #endregion
                   }





                   #endregion


                   #region Ambil JobDescID

                   query = @"SELECT JobDescID FROM transactions.JobDescription WHERE CreatedBy=@CreatedBy AND CreatedDate=@CreatedDate AND ProgramID=@ProgramID";

                   SqlCommand cmdJobDescID = new SqlCommand(query, conn);
                   cmdJobDescID.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                   cmdJobDescID.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                   cmdJobDescID.Parameters.Add("@ProgramID", SqlDbType.Int).Value = resultProgramID;
                  
                   cmdJobDescID.Transaction = txn;
                   var jobdesID = cmdJobDescID.ExecuteScalar();
                   int resultjobdesID = jobdesID == DBNull.Value ? 0 : int.Parse(jobdesID.ToString());
                   cmdJobDescID.Dispose();

                   #endregion


                   #region Ambil data JobDescRelationShipInternal berdasar ProgramIDTerakhir

                   query = @"SELECT * FROM transactions.JobDescRelationShipInternal WHERE ProgramID=@ProgramID";

                   SqlCommand cmdInternal = new SqlCommand(query, conn);
                   cmdInternal.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDLast;
                   cmdInternal.Transaction = txn;

                   ds = new DataSet();
                   dap = new SqlDataAdapter(cmdInternal);
                   dap.Fill(ds);
                   dap.Dispose();
                   cmdInternal.Dispose();
                   foreach (DataRow dr in ds.Tables[0].Rows)
                   {
                       #region Insert Data JobManagerialCompetency

                       query = "INSERT INTO transactions.JobDescRelationShipInternal(JobDescriptionID, Department, Purpose, ProgramID, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate) VALUES(@JobDescriptionID, @Department, @Purpose, @ProgramID, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate)";
                       SqlCommand cmdIntenalIns = new SqlCommand(query, conn);
                       cmdIntenalIns.CommandType = CommandType.Text;
                       cmdIntenalIns.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = resultjobdesID;// int.Parse(dr["JobDescriptionID"].ToString());
                       cmdIntenalIns.Parameters.Add("@Department", SqlDbType.VarChar).Value = dr["Department"].ToString();
                       cmdIntenalIns.Parameters.Add("@Purpose", SqlDbType.Text).Value = (object)dr["Purpose"].ToString() ?? DBNull.Value;
                       cmdIntenalIns.Parameters.Add("@ProgramID", SqlDbType.Int).Value = resultProgramID;
                       cmdIntenalIns.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                       cmdIntenalIns.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                       cmdIntenalIns.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value =  DBNull.Value;
                       cmdIntenalIns.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value =  DBNull.Value;


                       cmdIntenalIns.Transaction = txn;
                       cmdIntenalIns.ExecuteNonQuery();
                       cmdIntenalIns.Dispose();

                       #endregion
                   }

                   #endregion


                   #region Ambil data JobDescRelationShipExternal berdasar ProgramIDTerakhir

                   query = @"SELECT * FROM transactions.JobDescRelationShipExternal WHERE ProgramID=@ProgramID";

                   SqlCommand cmdExternal = new SqlCommand(query, conn);
                   cmdExternal.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDLast;
                   cmdExternal.Transaction = txn;

                   ds = new DataSet();
                   dap = new SqlDataAdapter(cmdExternal);
                   dap.Fill(ds);
                   dap.Dispose();
                   cmdExternal.Dispose();
                   foreach (DataRow dr in ds.Tables[0].Rows)
                   {
                       #region Insert Data JobDescRelationShipExternal

                       query = "INSERT INTO transactions.JobDescRelationShipExternal(JobDescriptionID, Department, Purpose, ProgramID, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate) VALUES(@JobDescriptionID, @Department, @Purpose, @ProgramID, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate)";
                       SqlCommand cmdExternalIns = new SqlCommand(query, conn);
                       cmdExternalIns.CommandType = CommandType.Text;
                       cmdExternalIns.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = resultjobdesID;// int.Parse(dr["JobDescriptionID"].ToString());
                       cmdExternalIns.Parameters.Add("@Department", SqlDbType.VarChar).Value = dr["Department"].ToString();
                       cmdExternalIns.Parameters.Add("@Purpose", SqlDbType.Text).Value = (object)dr["Purpose"].ToString() ?? DBNull.Value;
                       cmdExternalIns.Parameters.Add("@ProgramID", SqlDbType.Int).Value = resultProgramID;
                       cmdExternalIns.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                       cmdExternalIns.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                       cmdExternalIns.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = DBNull.Value;
                       cmdExternalIns.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = DBNull.Value;


                       cmdExternalIns.Transaction = txn;
                       cmdExternalIns.ExecuteNonQuery();
                       cmdExternalIns.Dispose();

                       #endregion
                   }


                   #endregion


                   #region Ambil data JobDescResponsibility berdasar ProgramIDTerakhir

                   query = @"SELECT * FROM transactions.JobDescResponsibility WHERE ProgramID=@ProgramID";

                   SqlCommand cmdResponsibility = new SqlCommand(query, conn);
                   cmdResponsibility.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDLast;
                   cmdResponsibility.Transaction = txn;

                   ds = new DataSet();
                   dap = new SqlDataAdapter(cmdResponsibility);
                   dap.Fill(ds);
                   dap.Dispose();
                   cmdResponsibility.Dispose();
                   foreach (DataRow dr in ds.Tables[0].Rows)
                   {
                       #region Insert Data JobDescResponsibility

                       query = "INSERT INTO transactions.JobDescResponsibility(JobDescriptionID, MainTask, Activities, ProgramID, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate) VALUES(@JobDescriptionID, @MainTask, @Activities, @ProgramID, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate)";
                       SqlCommand cmdResponIns = new SqlCommand(query, conn);
                       cmdResponIns.CommandType = CommandType.Text;
                       cmdResponIns.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = resultjobdesID;// int.Parse(dr["JobDescriptionID"].ToString());
                       cmdResponIns.Parameters.Add("@MainTask", SqlDbType.VarChar).Value = (object)dr["MainTask"].ToString() ?? DBNull.Value;
                       cmdResponIns.Parameters.Add("@Activities", SqlDbType.Text).Value = (object)dr["Activities"].ToString() ?? DBNull.Value;
                       cmdResponIns.Parameters.Add("@ProgramID", SqlDbType.Int).Value = resultProgramID;
                       cmdResponIns.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                       cmdResponIns.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                       cmdResponIns.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = DBNull.Value;
                       cmdResponIns.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = DBNull.Value;


                       cmdResponIns.Transaction = txn;
                       cmdResponIns.ExecuteNonQuery();
                       cmdResponIns.Dispose();


                       #region Ambil ResponsibilityID

                       query = @"SELECT ResponsibilityID FROM transactions.JobDescResponsibility WHERE CreatedBy=@CreatedBy AND CreatedDate=@CreatedDate AND ProgramID=@ProgramID ORDER BY ResponsibilityID DESC ";

                       SqlCommand cmdResID = new SqlCommand(query, conn);
                       cmdResID.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                       cmdResID.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                       cmdResID.Parameters.Add("@ProgramID", SqlDbType.Int).Value = resultProgramID;

                       cmdResID.Transaction = txn;
                       var resID = cmdResID.ExecuteScalar();
                       int resultresID = resID == DBNull.Value ? 0 : int.Parse(resID.ToString());
                       cmdResID.Dispose();

                       #endregion

                       #region Ambil data PerformanceIndicator berdasar ProgramIDTerakhir

                       query = @"SELECT * FROM transactions.PerformanceIndicator WHERE ProgramID=@ProgramID AND JobDescResponsibilityID=@JobDescResponsibilityID ";

                       SqlCommand cmdPI = new SqlCommand(query, conn);
                       cmdPI.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDLast;
                       cmdPI.Parameters.Add("@JobDescResponsibilityID", SqlDbType.Int).Value = int.Parse(dr["ResponsibilityID"].ToString().Trim());
                       cmdPI.Transaction = txn;

                       DataSet ds2 = new DataSet();
                       SqlDataAdapter dap2 = new SqlDataAdapter(cmdPI);
                       dap2.Fill(ds2);
                       dap2.Dispose();
                       cmdPI.Dispose();
                       foreach (DataRow dr2 in ds2.Tables[0].Rows)
                       {
                           #region Insert Data PerformanceIndicator


                           query = "INSERT INTO transactions.PerformanceIndicator(JobDescResponsibilityID, PI, ProgramID, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate) VALUES(@JobDescResponsibilityID, @PI, @ProgramID, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate)";
                           SqlCommand cmdPIIns = new SqlCommand(query, conn);
                           cmdPIIns.CommandType = CommandType.Text;
                           cmdPIIns.Parameters.Add("@JobDescResponsibilityID", SqlDbType.Int).Value = resultresID;// int.Parse(dr["JobDescResponsibilityID"].ToString());
                           cmdPIIns.Parameters.Add("@PI", SqlDbType.VarChar).Value = dr2["PI"].ToString();
                           cmdPIIns.Parameters.Add("@ProgramID", SqlDbType.Int).Value = resultProgramID;
                           cmdPIIns.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                           cmdPIIns.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                           cmdPIIns.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = DBNull.Value;
                           cmdPIIns.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = DBNull.Value;


                           cmdPIIns.Transaction = txn;
                           cmdPIIns.ExecuteNonQuery();
                           cmdPIIns.Dispose();

                           #endregion
                       }


                       #endregion


                       #endregion
                   }


                   #endregion


                  

                   


                   result = true;
                   txn.Commit();

               }
               catch (Exception ex)
               {
                   txn.Rollback();
                   throw (ex);
               }
               finally
               {
                   if (conn.State == ConnectionState.Open) conn.Close();
               }
           }

           return result;
       }

       public object GetProgramIDAktif()
       {
           object _result = null;
           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   StringBuilder sbQuery = new StringBuilder();
                   
                   sbQuery.AppendFormat("SELECT ProgramID FROM master.ReviewProgram WHERE RowStatus = 1");
                      

                   cmd.CommandText = sbQuery.ToString();
                   cmd.CommandType = CommandType.Text;
                   try
                   {
                       conn.Open();
                       _result = cmd.ExecuteScalar();
                   }
                   catch (Exception ex)
                   {
                       throw (ex);
                   }
                   finally
                   {
                       if (conn.State == ConnectionState.Open) conn.Close();
                   }
               }
           }
          

           return _result;
       }

       public object GetProgramIDLast()
       {
           object _result = null;
           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   StringBuilder sbQuery = new StringBuilder();

                   sbQuery.AppendFormat("SELECT TOP 1 ProgramID FROM master.ReviewProgram ORDER BY ProgramID DESC");


                   cmd.CommandText = sbQuery.ToString();
                   cmd.CommandType = CommandType.Text;
                   try
                   {
                       conn.Open();
                       _result = cmd.ExecuteScalar();
                   }
                   catch (Exception ex)
                   {
                       throw (ex);
                   }
                   finally
                   {
                       if (conn.State == ConnectionState.Open) conn.Close();
                   }
               }
           }


           return _result;
       }

       public bool OpenClose(ReviewProgram domain)
       {
           int result = 0;
           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = "UPDATE master.ReviewProgram SET RowStatus = @RowStatus, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE ProgramID = @ProgramID";
                   cmd.CommandType = CommandType.Text;
                   cmd.Parameters.Add("@ProgramID", SqlDbType.Int).Value = domain.ProgramID;
                   cmd.Parameters.Add("@RowStatus", SqlDbType.Bit).Value = (object)domain.RowStatus ?? DBNull.Value;
                   cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                   cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;

                   try
                   {
                       conn.Open();
                       result = cmd.ExecuteNonQuery();
                   }
                   catch (Exception ex)
                   {
                       throw (ex);
                   }
                   finally
                   {
                       if (conn.State == ConnectionState.Open) conn.Close();
                   }
               }
           }
           return (result > 0);
       }
    }
}
